package dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.PriorityDTO;
import dao.DAOException;
import dao.PriorityDAO;
import dao.mysql.util.*;

public class PriorityDAOImpl implements PriorityDAO {
	private Connection conn = null;
	
	public PriorityDTO findPriority(int priorityCode) throws DAOException {
		
		// TODO Auto-generated method stub
		try {
			 conn= MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
		// TODO Auto-generated catch block
			 e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		PriorityDTO pri = null;
	
		try {			
			conn.setAutoCommit(false);
			String sql = "SELECT * FROM Priority WHERE Code=? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setInt(1, priorityCode);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				pri = new PriorityDTO();
				pri.setCode(Integer.parseInt(rs.getString("Code")));
				pri.setLevel(rs.getString("Level"));
			}
			conn.commit();
			ps.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(pri.toString());
		return pri;
	}

	public ArrayList<PriorityDTO> findAllPriority() throws DAOException {
		
		try {
			 conn= MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
		// TODO Auto-generated catch block
			 e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PriorityDTO pri=null;
		ArrayList<PriorityDTO> items = new ArrayList<PriorityDTO>();
		
		try {			
			conn.setAutoCommit(false);
			String sql = "SELECT * FROM Priority;";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				pri = new PriorityDTO();
				pri.setCode(Integer.parseInt(rs.getString("Code")));
				pri.setLevel(rs.getString("Level"));
				items.add(pri);
			}
			conn.commit();
			ps.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	public void insertPriority(PriorityDTO pri) throws DAOException {
//		
		try {
			 conn= MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
		// TODO Auto-generated catch block
			 e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {			
			String sql = "Insert Into Priority Values (?,?) ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1,  pri.getCode());
			ps.setString(2,  pri.getLevel());
			ps.executeUpdate();
			
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + sql);
			ps.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void updatePriority(PriorityDTO pri) throws DAOException {
		try {
			 conn= MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
		// TODO Auto-generated catch block
			 e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {			
			conn.setAutoCommit(false);
			String sql = "Update Priority Set Level= ? Where Code= ? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1,  pri.getLevel());
			ps.setInt(2,  pri.getCode());
			
			ps.executeUpdate();
			
			conn.commit();
			ps.close();
		} catch (Exception e) {
			String error = "Error Updating Priority. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void deletePriority(PriorityDTO pri) throws DAOException {

		try {
			 conn= MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
		// TODO Auto-generated catch block
			 e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {			
			conn.setAutoCommit(false);
			String sql = "Delete From Priority Where Code= ? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, pri.getCode());
			ps.executeUpdate();
			
			conn.commit();
			ps.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			System.out.println("#$#$#@$#@#@$#@$#@$#$#@");
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	
}
